﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace web
{
    public class SqlDbHelper
    {
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["Dbconn"].ConnectionString; }
        }

        // 登录
        public static bool toLog(string userame, string password, string admin)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from users where username=@u and password=@p and admin=@a";
                User us = conn.Query<User>(sql, new { u = userame, p = password, a = admin }).FirstOrDefault();
                if (us != null)
                    return true;
                return false;
            }
        }

        //得到所有职员
        public static List<Staff> getAllStaff()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }
        //得到所有工资表
        public static List<allMoney> getAllMoney()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff, money where s_id = m_id";
                IEnumerable<allMoney> list = conn.Query<allMoney>(sql);
                return list.ToList();
            }
        }
        

        //添加职员
        public static bool addStaff(string user, Staff s)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                bool his = changeSatffHistory(user, 2);
                string sql = "insert into staff(s_name, s_sex, s_birth, s_native, s_degree, s_address, s_phone, s_standing, s_salary)" +
                    "values(@s_name, @s_sex, @s_birth, @s_native, @s_degree, @s_address, @s_phone, @s_standing, @s_salary)";
                int result = conn.Execute(sql, s);
                if (result != 0 && his)
                    return true;
                return false; ;
            }
        }

        //根据编号查
        public static List<Staff> getStaffById(string ID)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff where s_id=@s_id";
                IEnumerable<Staff> list = conn.Query<Staff>(sql, new { s_id = ID });
                return list.ToList();
            }
        }
        //根据姓名查
        public static Staff getStaffByName(string name)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff where s_name=@s_name";
                Staff s = conn.Query<Staff>(sql, new { s_name = name }).FirstOrDefault();
                return s;
            }
        }

        // 修改个人信息
        public static bool upSatff(string user, Staff s)
        {
            
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                bool his = changeSatffHistory(user, 1);
                string sql = "update staff " +
                    "set s_name=@s_name, s_sex=@s_sex, s_birth=@s_birth, s_native=@s_native, s_degree=@s_degree, s_address=@s_address, s_phone=@s_phone, s_standing=@s_standing, s_salary=@s_salary " +
                    "where s_id=@s_id";
                int result = conn.Execute(sql, s);
                if (result != 0 && his)
                    return true;
                return false; ;
            }
        }

        //添加修改记录
        public static bool changeSatffHistory(string user, int a)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "insert into operation(o_user, o_op) values(@u, @op)";  
                int result = conn.Execute(sql, new { u = user, op = a});
                if (result != 0)
                    return true;
                return false; ;
            }
        }

        //按id号删除
        public static bool deleteById(string user, int id)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                bool his = changeSatffHistory(user, 3);
                string sql = "delete from staff where s_id=@d";
                int result =  conn.Execute(sql, new { d = id });
                if (result != 0 && his)
                    return true;
                return false; ;
            }
        }

        //增加一个用户
        public static int insertUser(string username, string password, string admin)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "insert into users (username, password, admin) values(@u, @p, @a)";
                return conn.Execute(sql, new { u = username, p = password, a = admin });
            }
        }
        //id升序
        public static List<Staff> FindUsersByIdUp()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff order by s_id";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }

        //id降序
        public static List<Staff> FindUsersByIdDesc()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff order by s_id desc";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }

        //name升序
        public static List<Staff> FindUsersByNameUp()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff order by s_name";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }

        //name降序
        public static List<Staff> FindUsersByNameDesc()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff order by s_name desc";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }

        //standing升序
        public static List<Staff> FindUsersByStandingUp()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff order by s_standing";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }

        //standing降序
        public static List<Staff> FindUsersByStandingDesc()
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select * from staff order by s_standing desc";
                IEnumerable<Staff> list = conn.Query<Staff>(sql);
                return list.ToList();
            }
        }

        //计算薪水
        public static float salary(int staffid, int m_workhours, float m_insurance, float m_sum)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql = "select s_salary from staff where s_id = @sid";
                int s = conn.Query<Int32>(sql, new { sid = staffid }).FirstOrDefault();
                string sql2 = "select s_standing from staff where s_id = @sid";
                int year = conn.Query<Int32>(sql2, new { sid = staffid }).FirstOrDefault();
                return (s + 10 * m_workhours + m_insurance * year / 100.0f) * 0.9f - m_sum;
            }
        }


        //保存薪水
        public static int salary_save(int staffid, int m_workhours, float m_insurance, float m_sum)
        {
            using (IDbConnection conn = new SqlConnection(SqlDbHelper.ConnectionString))
            {
                string sql_id = "select * from money where m_id = @mid";
                List<Moeny> m = conn.Query<Moeny>(sql_id, new { mid = staffid }).ToList();

                if(m != null)
                {
                    string sql = "select s_salary from staff where s_id = @sid";
                    int s = conn.Query<Int32>(sql, new { sid = staffid }).FirstOrDefault();
                    string sql2 = "select s_standing from staff where s_id = @sid";
                    int year = conn.Query<Int32>(sql2, new { sid = staffid }).FirstOrDefault();
                    float salary_save = (s + 10 * m_workhours + m_insurance * year / 100.0f) * 0.9f - m_sum;

                    string sql3 = "update money set m_id = @uid, m_workhours = @w, m_sum = @i, m_salary = @sa";
                    return conn.Execute(sql3, new { uid = staffid, w = m_workhours, i = m_insurance, s = m_sum, sa = salary_save });
                }

                else
                {
                    string sql = "select s_salary from staff where s_id = @sid";
                    int s = conn.Query<Int32>(sql, new { sid = staffid }).FirstOrDefault();
                    string sql2 = "select s_standing from staff where s_id = @sid";
                    int year = conn.Query<Int32>(sql2, new { sid = staffid }).FirstOrDefault();
                    float salary_save = (s + 10 * m_workhours + m_insurance * year / 100.0f) * 0.9f - m_sum;

                    string sql3 = "insert into money (m_id, m_workhours, m_insurance, m_sum, m_salary) values(@uid, @w, @i, @s, @sa)";
                    return conn.Execute(sql3, new { uid = staffid, w = m_workhours, i = m_insurance, s = m_sum, sa = salary_save });
                }

                
            }
        }

    }
}